using Dapper;
using Lzfy_Refund_Service.Models;
using Lzfy_Refund_Service.Services;
using System.Text;

namespace Lzfy_Refund_Service.Repositories
{
    /// <summary>
    /// 退费任务完成情况仓储实现
    /// </summary>
    public class RefundTaskCompletionStatusRepository : IRefundTaskCompletionStatusRepository
    {
        private readonly IHisDGOracleConnectionFactory _hisDGOracleConnectionFactory;
        private readonly ILogger<RefundTaskCompletionStatusRepository> _logger;

        public RefundTaskCompletionStatusRepository(
            IHisDGOracleConnectionFactory hisDGOracleConnectionFactory,
            ILogger<RefundTaskCompletionStatusRepository> logger)
        {
            _hisDGOracleConnectionFactory = hisDGOracleConnectionFactory;
            _logger = logger;
        }

        /// <summary>
        /// 获取所有退费任务完成情况
        /// </summary>
        /// <returns>退费任务完成情况列表</returns>
        public async Task<IEnumerable<RefundTaskCompletionStatus>> GetAllAsync()
        {
            try
            {
                using var connection = _hisDGOracleConnectionFactory.CreateConnection();
                var sql = GetBaseSql();
                
                _logger.LogInformation("开始查询所有退费任务完成情况");
                var result = await connection.QueryAsync<RefundTaskCompletionStatus>(sql);
                _logger.LogInformation("查询所有退费任务完成情况成功，共 {Count} 条记录", result.Count());
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询所有退费任务完成情况失败");
                throw;
            }
        }

        /// <summary>
        /// 根据用户名获取退费任务完成情况
        /// </summary>
        /// <param name="userName">用户账号</param>
        /// <returns>退费任务完成情况列表</returns>
        public async Task<IEnumerable<RefundTaskCompletionStatus>> GetByUserNameAsync(string userName)
        {
            try
            {
                using var connection = _hisDGOracleConnectionFactory.CreateConnection();
                var sql = GetBaseSql() + " AND t1.user_name = :userName";
                
                _logger.LogInformation("开始查询用户 {UserName} 的退费任务完成情况", userName);
                var result = await connection.QueryAsync<RefundTaskCompletionStatus>(sql, new { userName });
                _logger.LogInformation("查询用户 {UserName} 的退费任务完成情况成功，共 {Count} 条记录", userName, result.Count());
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询用户 {UserName} 的退费任务完成情况失败", userName);
                throw;
            }
        }

        /// <summary>
        /// 根据用户姓名获取退费任务完成情况
        /// </summary>
        /// <param name="peopleName">用户姓名</param>
        /// <returns>退费任务完成情况列表</returns>
        public async Task<IEnumerable<RefundTaskCompletionStatus>> GetByPeopleNameAsync(string peopleName)
        {
            try
            {
                using var connection = _hisDGOracleConnectionFactory.CreateConnection();
                var sql = GetBaseSql() + " AND t1.people_name LIKE :peopleName";
                
                _logger.LogInformation("开始查询姓名包含 {PeopleName} 的退费任务完成情况", peopleName);
                var result = await connection.QueryAsync<RefundTaskCompletionStatus>(sql, new { peopleName = $"%{peopleName}%" });
                _logger.LogInformation("查询姓名包含 {PeopleName} 的退费任务完成情况成功，共 {Count} 条记录", peopleName, result.Count());
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询姓名包含 {PeopleName} 的退费任务完成情况失败", peopleName);
                throw;
            }
        }

        /// <summary>
        /// 根据查询条件获取退费任务完成情况
        /// </summary>
        /// <param name="request">查询请求</param>
        /// <returns>退费任务完成情况列表</returns>
        public async Task<IEnumerable<RefundTaskCompletionStatus>> GetByConditionsAsync(RefundTaskCompletionStatusQueryRequest request)
        {
            try
            {
                using var connection = _hisDGOracleConnectionFactory.CreateConnection();
                var sqlBuilder = new StringBuilder(GetBaseSql());
                var parameters = new DynamicParameters();

                // 添加条件
                if (!string.IsNullOrWhiteSpace(request.UserName))
                {
                    sqlBuilder.Append(" AND t1.user_name = :userName");
                    parameters.Add("userName", request.UserName);
                }

                if (!string.IsNullOrWhiteSpace(request.PeopleName))
                {
                    sqlBuilder.Append(" AND t1.people_name LIKE :peopleName");
                    parameters.Add("peopleName", $"%{request.PeopleName}%");
                }

                var sql = sqlBuilder.ToString();
                
                _logger.LogInformation("开始根据条件查询退费任务完成情况，用户账号: {UserName}, 用户姓名: {PeopleName}", 
                    request.UserName, request.PeopleName);
                var result = await connection.QueryAsync<RefundTaskCompletionStatus>(sql, parameters);
                _logger.LogInformation("根据条件查询退费任务完成情况成功，共 {Count} 条记录", result.Count());
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "根据条件查询退费任务完成情况失败，用户账号: {UserName}, 用户姓名: {PeopleName}", 
                    request.UserName, request.PeopleName);
                throw;
            }
        }

        /// <summary>
        /// 获取基础SQL查询语句
        /// </summary>
        /// <returns>SQL查询语句</returns>
        private static string GetBaseSql()
        {
            return @"
select 
    t1.people_name as PeopleName, --用户名
    t1.user_name as UserName,   --用户账号
    t1.task_amount as TaskAmount, --联系任务量
    nvl(m.take_task, 0) as TakeTask, -- 接收任务数
    nvl(m.contact_count, 0) as ContactCount, -- 已联系次数
    nvl(s.refunded_no_contact, 0) as RefundedNoContact, -- 已退款无需联系
    nvl(s.connected_informed, 0) as ConnectedInformed, -- 已拨通并告知
    nvl(s.unreachable_empty, 0) as UnreachableEmpty, -- 号码无法拨通(空号)
    nvl(s.connected_not_self, 0) as ConnectedNotSelf, -- 已拨通非本人
    nvl(s.connected_no_answer, 0) as ConnectedNoAnswer, -- 已拨通无人接听
    nvl(s.number_outage, 0) as NumberOutage, -- 号码无法拨通(停机)
    nvl(s.mistaken_fraud, 0) as MistakenFraud, -- 被误解为诈骗电话
    nvl(s.no_call_needed, 0) as NoCallNeeded, -- 无需电话通知
    nvl(s.total_records, 0) as TotalRecords -- 总记录数
from uum.uum_user t1
left join (
    -- 聚合主任务表和明细表的数据
    select 
        tb1.username,
        count(tb2.id) as take_task, -- 接收任务总数（明细明细记录数）
        sum(case when tb2.contact_count > 0 then 1 else 0 end) as contact_count
    from pca.lzfy_refund_task_master tb1
    join pca.lzfy_refund_task_detail tb2 on tb1.id = tb2.master_id
    group by tb1.username
) m on t1.user_name = m.username
left join (
    -- 聚合视图数据
    select 
        tb1.username,
        sum(tb2.已退款无需联系_count) as refunded_no_contact,
        sum(tb2.已拨通并告知_count) as connected_informed,
        sum(tb2.号码无法拨通空号_count) as unreachable_empty,
        sum(tb2.已拨通非本人_count) as connected_not_self,
        sum(tb2.已拨通无人接听_count) as connected_no_answer,
        sum(tb2.号码无法拨通停机_count) as number_outage,
        sum(tb2.被误解为诈骗电话_count) as mistaken_fraud,
        sum(tb2.无需电话通知_count) as no_call_needed,
        sum(tb2.total_records) as total_records
    from pca.lzfy_refund_task_master tb1
    join PCA.LZFY_TASK_NOTES_SUMMARY tb2 on tb1.id = tb2.master_id
    group by tb1.username
) s on t1.user_name = s.username
where t1.task_amount > 0";
        }
    }
}